﻿using DBUtil.Attributes;
using NUnit.Framework;
using Shouldly;
using System.ComponentModel.DataAnnotations.Schema;

namespace Test.MySql.Selects.SubSelects
{
    [TestFixture]
    internal class SelectBuilder2 : TestBase
    {
        #region model
        [Table("test")]
        public class AEntity
        {
            [Column("a_id")]
            [PrimaryKey,Identity]
            public int Id { get; set; }
            [Column("a_name")]
            public string Name { get; set; }
            [Column("a_addr")]
            public string Addr { get; set; }
            public int Age { get; set; }
        }
        [Table("test2")]
        public class BEntity
        {
            [Column("b_id")]
            [PrimaryKey,Identity]
            public int Id { get; set; }
            [Column("b_name")]
            public string Name { get; set; }
            [Column("b_addr")]
            public string Addr { get; set; }
            public int Age { get; set; }
        }
        #endregion

        [Test]
        public void FirstOrDefault()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).FirstOrDefault((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.`a_id` `a.Id`,t.`a_name` `a.Name`,t.`a_addr` `a.Addr`,t.`Age` `a.Age`,t2.`b_id` `b.Id`,t2.`b_name` `b.Name`,t2.`b_addr` `b.Addr`,t2.`Age` `b.Age`
                from `test` t
                    left join `test2` t2 on t.`a_id` = t2.`b_id`
                where t2.`b_id` = (ifnull((select sub_t.`a_id`
                from `test` sub_t
                    left join `test2` sub_t2 on sub_t.`a_id` = sub_t2.`b_id`
                where sub_t.`a_id` = 10
                limit 1),0));
                """);
        }

        [Test]
        public void ToList()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).ToList((a, b) => a.Id).Contains(i.t2.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.`a_id` `a.Id`,t.`a_name` `a.Name`,t.`a_addr` `a.Addr`,t.`Age` `a.Age`,t2.`b_id` `b.Id`,t2.`b_name` `b.Name`,t2.`b_addr` `b.Addr`,t2.`Age` `b.Age`
                from `test` t
                    left join `test2` t2 on t.`a_id` = t2.`b_id`
                where t2.`b_id` in (select sub_t.`a_id`
                from `test` sub_t
                    left join `test2` sub_t2 on sub_t.`a_id` = sub_t2.`b_id`
                where sub_t.`a_id` = 10);
                """);
        }

        [Test]
        public void Max()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).MaxOrDefault((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.`a_id` `a.Id`,t.`a_name` `a.Name`,t.`a_addr` `a.Addr`,t.`Age` `a.Age`,t2.`b_id` `b.Id`,t2.`b_name` `b.Name`,t2.`b_addr` `b.Addr`,t2.`Age` `b.Age`
                from `test` t
                    left join `test2` t2 on t.`a_id` = t2.`b_id`
                where t2.`b_id` = (ifnull((select max(sub_t.`a_id`)
                from `test` sub_t
                    left join `test2` sub_t2 on sub_t.`a_id` = sub_t2.`b_id`
                where sub_t.`a_id` = 10),0));
                """);
        }

        [Test]
        public void Min()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).MinOrDefault((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.`a_id` `a.Id`,t.`a_name` `a.Name`,t.`a_addr` `a.Addr`,t.`Age` `a.Age`,t2.`b_id` `b.Id`,t2.`b_name` `b.Name`,t2.`b_addr` `b.Addr`,t2.`Age` `b.Age`
                from `test` t
                    left join `test2` t2 on t.`a_id` = t2.`b_id`
                where t2.`b_id` = (ifnull((select min(sub_t.`a_id`)
                from `test` sub_t
                    left join `test2` sub_t2 on sub_t.`a_id` = sub_t2.`b_id`
                where sub_t.`a_id` = 10),0));
                """);
        }

        [Test]
        public void Sum()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).SumOrDefault((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.`a_id` `a.Id`,t.`a_name` `a.Name`,t.`a_addr` `a.Addr`,t.`Age` `a.Age`,t2.`b_id` `b.Id`,t2.`b_name` `b.Name`,t2.`b_addr` `b.Addr`,t2.`Age` `b.Age`
                from `test` t
                    left join `test2` t2 on t.`a_id` = t2.`b_id`
                where t2.`b_id` = (ifnull((select sum(sub_t.`a_id`)
                from `test` sub_t
                    left join `test2` sub_t2 on sub_t.`a_id` = sub_t2.`b_id`
                where sub_t.`a_id` = 10),0));
                """);
        }

        [Test]
        public void Avg()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).AvgOrDefault((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.`a_id` `a.Id`,t.`a_name` `a.Name`,t.`a_addr` `a.Addr`,t.`Age` `a.Age`,t2.`b_id` `b.Id`,t2.`b_name` `b.Name`,t2.`b_addr` `b.Addr`,t2.`Age` `b.Age`
                from `test` t
                    left join `test2` t2 on t.`a_id` = t2.`b_id`
                where t2.`b_id` = (ifnull((select avg(sub_t.`a_id`)
                from `test` sub_t
                    left join `test2` sub_t2 on sub_t.`a_id` = sub_t2.`b_id`
                where sub_t.`a_id` = 10),0));
                """);
        }
    }
}
